package com.tedu.jdbc;

import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import javax.sql.DataSource;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@SpringBootTest
public class NoticeJDBC {
    @Autowired
    private DataSource dataSource;
    @Test
    void noticeJdbcTest() throws SQLException {
       Connection conn=dataSource.getConnection();
       String sql="INSERT INTO sys_notice " +
               "(title,content,type,status,createdTime," +
               "createdUser,modifiedTime,modifiedUser )" +
               "values (?,?,?,?,?,?,?,?)";
       PreparedStatement ps=conn.prepareStatement(sql);
       ps.setString(1, "听课通知");
       ps.setString(2, "4月5号");
       ps.setString(3, "0");
       ps.setString(4, "1");
       ps.setTimestamp(5,
                new Timestamp(System.currentTimeMillis()));
       ps.setString(6,"nick");
       ps.setTimestamp(7,
                new Timestamp(System.currentTimeMillis()));
       ps.setString(8,"diudiu");
       ps.execute();
       ps.close();
       conn.close();
    }
    @Test
    void noticeSelectNoticeTest() throws SQLException{
        Connection con=dataSource.getConnection();
        String sql="SELECT id,title,content, " +
                "status,type,createdTime "+
                "FROM sys_notice WHERE id>=?";
        PreparedStatement ps=con.prepareStatement(sql);
        ps.setInt(1, 2);
        boolean flag=ps.execute();
        ResultSet rs=null;
        if(flag){
            rs=ps.getResultSet();
            List<Map<String,Object>> list=new ArrayList();
            while (rs.next()){
                Map<String,Object> map=new HashMap();
                map.put("id", rs.getInt("id"));
                map.put("title",rs.getString("title"));
                map.put("content", rs.getString("content"));
                map.put("status",rs.getString("status"));
                map.put("type", rs.getString("type"));
                map.put("createdTime", rs.getTimestamp("createdTime"));
                list.add(map);
                System.out.println(list);
            }
        }

        rs.close();
        ps.close();
        con.close();
    }
    @Test
    void noticeSelectTest() throws SQLException {
            //1.建立连接
            Connection conn = dataSource.getConnection();
            //2.创建 Statement
            String sql = "select id,title,content,status,type,createdTime "
                    + " from sys_notice where id>=?";
            PreparedStatement pstmt = conn.prepareStatement(sql);
            //3.发送 sql(发送到数据库)
            pstmt.setInt(1, 2);
            boolean flag = pstmt.execute();
            //4.处理结果
            ResultSet rs = null;
            if (flag) {//true 表示查询,有结果集
                //获取结果集(二維表結構)
                rs = pstmt.getResultSet();
                List<Map<String, Object>> list = new ArrayList<>();
                //获取结果集中的元数据(表名，字段名)
                ResultSetMetaData rsmd = rs.getMetaData();
                while (rs.next()) {//一行记录应为一个 map 对象 (行映射)
                    Map<String, Object> map = new HashMap();
                    //将取出类的数据存储到 map (key 为字段名,值为字段 value)
                    for (int i = 1; i <= rsmd.getColumnCount(); i++) {
                        //getColumnCount();獲取列的數量
                        map.put(rsmd.getColumnLabel(i),
                                rs.getObject(rsmd.getColumnLabel(i)));
                        //rsmd.getColumnLabel(i) 获取第 i 列的名字
                    }
                    //将每行记录对应的 map 对象存储到 list 集合
                    System.out.println(map);
                    list.add(map);
                }

            }
            //5.释放资源
             rs.close();
             pstmt.close();
             conn.close();
        }
    }


